Create Unpivot & Pivot Tables in Staging Database and Populate the Staging Tables 7
Click and download the SQL files and Store Procedures.
Run Script that is attached to create your Staging tables in the Staging Database. Once ran the tables will be made up of these columns.
*Using SSIS Toolbox, the following components will be drag in the Control Flow Dashboard:
1. Drag and drop the Excel Source – Double click and add the excel connection manager and select the sheet from the excel to load the data
2. Drag and drop the Conditional Split - Double click and add condition to remove the Null values
3.
Drag and drop the Data
Conversion – Select all dates and change the data type to Unicode string (255)
4. Drag and drop the Unpivot
transformation – double click and then selects the Data Conversion date column –
in Destination column – Name the column as “QTY” and Pivot key value column
name as “QuarterlyDate”
5.. Drag and drop the Derived Column transformation – Add column “Filename” and add the source file name in the expression and make changes to “QuarterlyDate” column using this Code -
RTRIM(LTRIM(SUBSTRING(QuarterlyDate,FINDSTRING(QuarterlyDate + "_","_",1) + 1,LEN(QuarterlyDate))))6. Drag and drop the Data
Conversion and select QTY and change the data type to Unicode String (4000)
7. Drag and drop the Destinationl Source – Double click and add the OLE- DB connection manager and select the destination table
Repeat the all the Steps for loading all the Business Property Type Source Files
The UnPivot & Pivot Staging tables are populated with the data from the Source Files
Merge all Pivot Tables into One Single Table
Drag and Drop the Data flow task and rename it - double click -
Drag and drop the OLE-DB Source file and double click and add source connection manager and select the source table
Drag and drop the derived column - use the Derived column Code
Code used for Derived Column -
(DT_WSTR,8)REPLACE(REPLACE(Quarterlydate,"/",""),"/","")
Getdate()
Drag and drop the derived column - use the Derived column Code
Code used for Derived Column1-
RIGHT("0" + QD,8)Drag and drop the derived column - use the Derived column Code
Code used for Derived Column2-
(SUBSTRING(QD,5,4) + "-" + SUBSTRING(QD,1,2) + "-" + SUBSTRING(QD,3,2))
Drag and drop the data conversion and make data conversion of the data to match the destination table
Drag and drop the OLE-DB destination Source and add destination connection manager and select the destination table
Repeat the Steps for rest of the business properties type
Populated all Pivoted Tables in staging after excuting the package
Continued next step